package com.comfacauca.xmlc2.utils.tablas;


import org.apache.log4j.Logger;

/**
 * The Query class is used internally by an instance of Editor.  It encapsulates
 * all the SQL (Structured Query Language) syntax required to interface with any
 * database that supports SQL. 
 * @author Alan Shiers
 *
 */

public class Query
{
	public static enum Type {SELECT{
		public String toString()
		{
			return "SELECT ";
		}
	},INSERT{
		public String toString()
		{
		  return "INSERT ";	
		}
	},UPDATE{
	    public String toString()
	    {
	      return "UPDATE ";	
	    }
	},DELETE{
	    public String toString()
	    {
	      return "DELETE ";	
	    }
	}
	};
	
	public static enum FilterLogicalOperator {AND{
		 public String toString()
		 {
			 return " AND ";
		 }
	},OR{
		public String toString()
		{
			return " OR ";
		}
	}};
	
	protected final static String FROM = " FROM ";
	protected final static String WHERE = " WHERE ";
	protected final static String LIMIT = " LIMIT ";
	protected final static String ORDER = " ORDER BY ";
	protected Type type = Type.SELECT; //Default
	protected Database dbconn = null;
	protected String[] tables = null;
	protected Field[] fields = null;
    protected WhereCondition[] where = null;
	protected Order[] order = null;
	protected int limit = -1;
	protected int offset = -1;	
	private FilterLogicalOperator flo = FilterLogicalOperator.AND;//Default
	private String NewRecordID = "";
	private String pKey = "id"; //Default
	private int iFilteredTotal = -1;
	private int iTotal = -1;
	private String parentTableAlias = "";
	private boolean isLinkTable = false;
	private boolean listAllTables = false;
	private Logger logger;
	
	
	public Query(Type t)
	{
		if(t != Type.SELECT) //SELECT IS THE DEFAULT
		   type = t;
		logger = Logger.getLogger(Query.class);
	}
	
	public Query(Type t, String[] tbls)
	{
		if(t != Type.SELECT) //SELECT IS THE DEFAULT
		   type = t;
		tables = tbls;
		logger = Logger.getLogger(Query.class);
	}
	/**
	 * For a query the default is not to list all tables.
	 * If you set the value to true, the tables will be listed
	 * separated by commas.  This makes it possible to perform
	 * old style joins between multiple tables.<br>
	 * i.e.: SELECT employees.first_name, employees.last_name, 
     * landlines.number FROM employees,landlines WHERE employees.id=landlines.emp_id;
	 */
	public void setListAllTables(boolean value)
	{
		listAllTables = value;
	}
	
	/**
	 * Inquire if the table is a Link Table
	 * @return
	 */
	public boolean isLinkTable()
	{
		return isLinkTable;
	}
	/**
	 * Set this property if the table being dealt with is a Link Table.
	 * @param value
	 */
	public void setAsLinkTable(boolean value)
	{
		isLinkTable = value;
	}
	
	/**
	 * Set an alias name for the Parent table.
	 * @param value
	 */
	public void setAliasParentTableName(String value)
	{
	  parentTableAlias = value;
	}
	/**
	 * Get the alias name of the Parent table  
	 * @return
	 */
	public String getAliasParentTableName()
	{
	  return parentTableAlias;
	}
	/**
	 * Set the name of the Primary Key field of the Parent table.
	 * @param value
	 */
	public void setPrimaryKey(String value)
	{
		pKey = value;
	}
	/**
	 * Get the name of the Primary Key field of the Parent table.
	 * @return A String
	 */
	public String getPrimaryKey()
	{
		return pKey;
	}
	/**
	 * Set the value for iFilteredTotal.
	 * @param value
	 */
	public void setIFilteredTotal(int value)
	{
		iFilteredTotal = value;
	}
	/**
	 * Get the value of iFilteredTotal.
	 * @return An int
	 */
	public int getIFilteredTotal()
	{
		return iFilteredTotal;
	}
	/**
	 * Set the value for iTotal.
	 * @param value
	 */
	public void setITotal(int value)
	{
		iTotal = value;
	}
	/**
	 * Get the value of iTotal
	 * @return An int
	 */
	public int getITotal()
	{
		return iTotal;
	}
	
	/**
	 * Set the ID value obtained after a new record
	 * has been inserted into a table.
	 * @param str A String value
	 */
	public void setNewRecordID(String str)
	{
		NewRecordID = str;
	}
	/**
	 * Get the ID value obtained after a new record
	 * has been inserted into a table.
	 * @return String
	 */
	public String getNewRecordID()
	{
		return NewRecordID;
	}
	/**
	 * Set a reference to an instance of the Database class.
	 * @param db
	 */
	public void setDatabase(Database db)
	{
		dbconn = db;
	}
	/**
	 * Get a reference to an instance of the Database class.
	 * @return Database
	 */
	public Database getDatabase()
	{
		return dbconn;
	}
	/**
	 * Set the Fields to be used in this query.	
	 * @param flds
	 */
	public void setFields(Field[] flds)
	{
		fields = flds;
	}
	/**
	 * Get the Fields being used in this query.
	 * @return Field[] array
	 */
	public Field[] getFields()
	{
		return fields;
	}
	
	protected String[] getFieldsAsStringArray()
	{
		String[] temp = new String[fields.length + 1];
		temp[0] = pKey;
		int count = 1;
		for(int i = 0; i < fields.length; i++)
		{
			temp[count] = fields[i].getDBFieldName();
			count++;
		}
		return temp;
	}
	
	protected String[] getFieldsAsStringArrayWithoutID()
	{
		String[] temp = new String[fields.length];		
		
		for(int i = 0; i < fields.length; i++)
		{
			temp[i] = fields[i].getDBFieldName();			
		}
		return temp;
	}
	
	/**
	 * Set the Order - example: ORDER BY <i>fieldName</i> asc
	 * @param orders An Order[] array
	 */
	public void setOrder(Order[] orders)
	{
		order = orders;
	}
	/**
	 * Set the logical operator as part of the WHERE filter when you have 
	 * multiple conditions.<br>
	 * Example: WHERE <i>field1Name</i> = <i>someValue</i> AND <i>field2Name</i> = <i>someOtherValue</i>
	 * The default is the AND operator. You can change it to OR.
	 * Please use the enum provided in this class.
	 * 
	 * @param operator
	 */
	public void setFilterLogicalOperator(FilterLogicalOperator operator)
	{
		if(flo != operator)
		   flo = operator;  
	}
	/**
	 * Get the logical operator being used as part of the WHERE filter.
	 * @return A FilterLogicalOperator
	 */
	public FilterLogicalOperator getFilterLogicalOperator()
	{
		return flo;
	}
	/**
	 * Set the Query Type.
	 * @param t
	 */
	public void setQueryType(Type t)
	{
		type = t;
	}
	/**
	 * Get the Query Type.
	 * @param tbls
	 */
	public void setTables(String[] tbls)
	{
		tables = tbls;
	}
	/**
	 * Get the tables being used in this query
	 * @return
	 */
	public String[] getTables()
	{
		return tables;
	}
	/**
	 * Get the list of tables being used in this query as a comma
	 * delimited String.
	 * @return A String
	 */
	public String getTablesAsString()
	{
		String str = "";
		for(int i = 0; i < tables.length;i++)
		{
			str += tables[i] + ",";
		}
		str = str.substring(0,str.length() - 1);
		return str;
	}
	
	public String getParentTable()
	{
		return tables[0];
	}
	
	/**
	 * Get the Query Type.
	 * @return Type
	 */
	public Type getQueryType()
	{
		return type;
	}
	/**
	 * Set all the WhereConditions for this query.
	 * @param whereclauses
	 */
	public void setWhereConditions(WhereCondition[] whereclauses)
	{
		where = whereclauses;
	}
	/**
	 * Get the array of WhereCondition(s)
	 * @return
	 */
	public WhereCondition[] getWhereConditions()
	{
		return where;
	}
	/**
	 * Inquire if there is any Where conditions
	 * @return true or false
	 */
	public boolean haveWhereConditions()
	{
		if(where == null)
			return false;
		return true;
	}
	//This just delimits any elements in an array with commas
	//such as the list of fields and tables
	private String prepare(Object[] array)
	{
		if(array == null)return "";		
		String temp = "";
		for(int i = 0; i < array.length; i++)
		{
			temp += array[i].toString() + ",";
		}
		temp = temp.substring(0, temp.length() - 1);
		return temp;
	}
	
	private String prepareFields(Object[] array)
	{
		if(array == null)return "";
		String temp = "";		
		for(int i = 0; i < array.length; i++)
		{
			temp += array[i].toString() + ",";
		}
		temp = temp.substring(0, temp.length() - 1);
		return temp;
	}
	
	private String prepareFieldsWithID(Object[] array)
	{
		if(array == null)return "";
		String temp = "";
		String idField = getParentTable() + "." + pKey;
		temp = idField + ",";
		for(int i = 0; i < array.length; i++)
		{
			temp += array[i].toString() + ",";
		}
		temp = temp.substring(0, temp.length() - 1);
		return temp;
	}
	
	protected String prepareTables(String[] array)
	{
		if(array == null)return "";
		/*This little condition will prevent old style joins if the JOIN class is non-existant.
		//If the JOIN class is non-existant, it is an indicator that this is a trial version and 
		//therefore, Joins are not permitted unless the owner pays for the full version.
		if(classExists("com.tacticalenterprisesltd.Join") == false)
		{
			if(listAllTables)
			{			  
			  logger.error("JED - Trial Version does not support Joins");
			}
			return array[0];
		}
		*/
		if(listAllTables)
		{
		  String temp = "";
		
		  for(int i = 0; i < array.length; i++)
		  {
			temp += array[i].toString() + ",";
		  }
		  //Get rid of the last comma
		  temp = temp.substring(0, temp.length() - 1);		  
		
		  return temp;
		}
		
		return array[0];
	}
	
	/*
	private boolean classExists(String clazz)
	{
		boolean exists = true;
		try{
			Class.forName(clazz);
		}
		catch(ClassNotFoundException fnfe)
		{
			exists = false;
		}
		return exists;
	}
	*/
	
	protected String prepareValues()
	{
		if(fields == null)return "";
		String temp = "";
		//The id field value needs to be inserted automatically by RDBMS, so we just provide a dummy value here.
		if(dbconn == null)
		{
			logger.error("You didn't provide a reference to the Database by calling Query.setDatabase(Database db).");
		}
		if(dbconn.dbtype == Database.RDBMS.MYSQL)
		   temp = "0,"; 
		else if(dbconn.dbtype == Database.RDBMS.ORACLE)
		   temp = getParentTable().toUpperCase() + "_SEQ.NEXTVAL,";	
		for(int i = 0; i < fields.length; i++)
		{
			if(fields[i].getFieldType() == Field.Type.DATE)
			{	
				if(fields[i].getDBDateValue() instanceof Integer)
				{
				  Integer value = (Integer)fields[i].getDBDateValue();
				  if(value == java.sql.Types.NULL)
				  {
					  temp += "null,";
				  }
				}
				else
				{
				  temp += "'" + fields[i].getDBDateValue() + "'" + ",";
				}
			}
			else if(fields[i].getFieldType() == Field.Type.STRING)
				temp += "'" + fields[i].getValue().toString() + "'" + ",";
			else
			    temp += fields[i].getValue().toString() + ",";
		}
		temp = temp.substring(0, temp.length() - 1);
		return temp;
	}
	
		
	protected String prepareLinkTableValues()
	{
		if(fields == null)return "";
		String temp = "";		
		for(int i = 0; i < fields.length; i++)
		{
			if(fields[i].getFieldType() == Field.Type.DATE)
			{	
				if(fields[i].getDBDateValue() instanceof Integer)
				{
				  Integer value = (Integer)fields[i].getDBDateValue();
				  if(value == java.sql.Types.NULL)
				  {
					  temp += "null,";
				  }
				}
				else
				{
				  temp += "'" + fields[i].getDBDateValue() + "'" + ",";
				}
			}
			else if(fields[i].getFieldType() == Field.Type.STRING)
				temp += "'" + fields[i].getValue().toString() + "'" + ",";
			else
			    temp += fields[i].getValue().toString() + ",";
		}
		temp = temp.substring(0, temp.length() - 1);
		return temp;
	}
	
	//This prepares the WHERE clause 
	protected String prepareWhere(WhereCondition[] array)
	{
		if(array == null)return "";
		//System.out.println("Query.prepareWhere - array.length: " + array.length);
		String temp = WHERE;
		for(int i = 0; i < array.length; i++)
		{
			temp += array[i].toString() + flo.toString();
		}
		//System.out.println("before: " + temp);
		temp = temp.substring(0, temp.length() - flo.toString().length());
		//System.out.println("after: " + temp);
		return temp;
	}
	//This prepares the ORDER BY clause specifically.
	protected String prepareOrder(Order[] array)
	{
		if(array == null)return "";
		String temp = ORDER;
		for(int i = 0; i < array.length; i++)
		{
			temp += array[i].toString() + ",";
		}
		temp = temp.substring(0, temp.length() - 1);
		return temp;	
	}
	//This prepares the LIMIT clause specifically.
	protected String prepareLimit(int start, int recordsToReturn)
	{
		if(recordsToReturn == -1 && start == -1)return "";
		String temp = "";
		if(start == -1 && recordsToReturn > -1)
		   temp = LIMIT + recordsToReturn;
		else if(start > -1 && recordsToReturn > -1)
			temp = LIMIT + start + ", " + recordsToReturn;
		return temp;
	}
	
	
	//The way this is supposed to work is that before calling this method,
	//at some point the fields should have been iterated over and the field
	//values changed.
	protected String prepareSet(Field[] flds)
	{
		if(flds.length == 0) return "";
		String set = "";
		Field field = null;
		for(int i = 0; i < flds.length; i++)
		{
			field = flds[i];
			if(field.getFieldType() == Field.Type.DATE)
			{
			   if(field.getDBDateValue() instanceof Integer) 
			   {
				   Integer value = (Integer)field.getDBDateValue();
				   if(value == java.sql.Types.NULL)
				      set += field.getDBFieldName() + "=null,";
			   }
			   else
			   {
			     set += field.getDBFieldName() + "='" + field.getDBDateValue() + "',";
			   }
			}
			else if(field.getFieldType() == Field.Type.STRING)
			   set += field.getDBFieldName() + "='" + field.getValue() + "',";
			else
			   set += field.getDBFieldName() + "=" + field.getValue() + ",";
		}
		set = set.substring(0, set.length() - 1);
		return set;
	}
	
	/**
	 * Create the SQL query string to select one or more
	 * rows of data from the database.
	 * @return
	 */
	private String AssembleSelect()
	{	
		if(fields == null)
		{
			logger.error("You didn't set the array of Fields for the Query object.");
		}
		if(tables == null)
		{
			logger.error("You didn't set the array of Tables for the Query object.");
		}
		return type.toString() + prepare(fields) + FROM + prepareTables(tables) + parentTableAlias + prepareWhere(where) + prepareOrder(order) + prepareLimit(offset,limit);
	}
	/**
	 * Create the SQL query string to insert a new row
	 * of data into the database.
	 * @return String
	 */
	private String AssembleInsert()
	{ 
		String query = "";
		if(fields == null)
		{
			logger.error("You didn't set the array of Fields for the Query object.");
		}
		if(tables == null)
		{
			logger.error("You didn't set the array of Tables for the Query object.");
		}
		if(isLinkTable == false)
		    query = type.toString() + "INTO " + prepareTables(tables) + " (" + prepareFieldsWithID(fields) + ") VALUES (" + prepareValues() + ")";
		else
			query = type.toString() + "INTO " + prepareTables(tables) + " (" + prepareFields(fields) + ") VALUES (" + prepareLinkTableValues() + ")";
		return query;
	}
	/**
	 * Create the SQL query string to update an existing
	 * row of data in the database.
	 * @return String
	 */
	private String AssembleUpdate()
	{
		if(fields == null)
		{
			logger.error("You didn't set the array of Fields for the Query object.");
		}
		if(tables == null)
		{
			logger.error("You didn't set the array of Tables for the Query object.");
		}
		return type.toString() + prepareTables(tables) + " SET " + prepareSet(fields) + prepareWhere(where);
	}
	/**
	 * Create the SQL query string to delete a row of data
	 * from the database.
	 * @return String
	 */
	private String AssembleDelete()
	{
		if(tables == null)
		{
			logger.error("You didn't set the array of Tables for the Query object.");
		}
		return type.toString() + FROM + prepareTables(tables) + prepareWhere(where);
	}
	/**
	 * Set the Offset on a SELECT query when limiting the number of records
	 * to be returned.
	 * @param value
	 */
	public void setOffset(int value)
	{
		offset = value;
	}
	/**
	 * Get the Offset on a SELECT query when limiting the number of records
	 * to be returned.
	 * @return
	 */
	public int getOffset()
	{
		return offset;
	}
	/**
	 * Set the Limit on a SELECT query when limiting the number of records
	 * to be returned.
	 * @param value
	 */
	public void setLimit(int value)
	{
		limit = value;
	}
	/**
	 * Get the Offset on a SELECT query when limiting the number of records
	 * to be returned.
	 * @return
	 */
	public int getLimit()
	{
		return limit;
	}
	/**
	 * Get the actual query produced by this class. It will produce either
	 * an INSERT,UPDATE,DELETE, or SELECT query statement.
	 * @return An SQL String
	 */
	@Override
	public String toString()
	{
		String statement = "";
		if(type == Type.SELECT)
		{
			return AssembleSelect();
		}
		else if(type == Type.INSERT)
		{
			return AssembleInsert();
		}
		else if(type == Type.UPDATE)
		{
			return AssembleUpdate();
		}
		else if(type == Type.DELETE)
		{
			return AssembleDelete();
		}
		return statement;
	}
}

